from django.db import models
from django.db import connection
import time
# Create your models here.

class FreightManager(models.Manager):
    COLUMN = ("FREIGHT_ID","FORWARDER","TRACKING_NR","WEIGHT","PALLET","BOXES","AMOUNT","RECEIVING_DATE")
    def query_freight(self):
        cursor = connection.cursor()
        cursor.execute("""
            SELECT freight_maininfo.FREIGHT_ID,(select distinct FORWARDER from recv_prealert_header where freight_maininfo.tracking_nr=recv_prealert_header.tracking_nr) as FOWARDER,
            freight_maininfo.TRACKING_NR, freight_maininfo.WEIGHT, freight_maininfo.PALLET, 
				freight_maininfo.BOXES, freight_maininfo.AMOUNT,
            (select distinct unix_timestamp(RECV_DATE) from recv_prealert_header where freight_maininfo.tracking_nr=recv_prealert_header.tracking_nr) as RECEIVING_DATE 
            FROM freight_maininfo""")
        table = cursor.fetchall()
        result = []
        for row in table:
            dic = {"FREIGHT_ID":row[0], "FORWARDER":row[1], "TRACKING_NR":row[2], "WEIGHT":row[3], "PALLET":row[4], "BOXES":row[5],"AMOUNT":row[6],"RECEIVING_DATE":time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(row[7])),}
            result.append(dic)
        return result

class maininfo(models.Model):
    FREIGHT_ID = models.AutoField(primary_key=True)
    TRACKING_NR = models.CharField(max_length = 45)
    WEIGHT = models.FloatField(null=True, default=0.00)
    PALLET = models.CharField(max_length=15, null=True, default='')
    BOXES = models.CharField(max_length=15, null=True, default='')
    AMOUNT = models.FloatField(null=True, default=0.00)
    objects = FreightManager()